package com.action;

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import com.bean.InstrumentBean;
import com.bean.RecordBean;
import com.bean.UserBean;
import com.dao.StatisticDao;
import com.exception.DBException;
import com.exception.StatisticException;

public class SheetAction
{
	private WritableCellFormat getTitleFormat()
	{
		WritableFont titleFont = new WritableFont(WritableFont.TAHOMA, 13,WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE,Colour.DARK_BLUE);
		WritableCellFormat titleFormat = new WritableCellFormat (titleFont);
		try
		{
			titleFormat.setAlignment(Alignment.CENTRE);
		}
		catch (WriteException e)
		{
		}
		return titleFormat;
	}
	private WritableCellFormat getContentFormat()
	{
		WritableCellFormat cellFormat = new WritableCellFormat ();
		try
		{
			cellFormat.setAlignment(Alignment.CENTRE);
		}
		catch (WriteException e)
		{
		}
		return cellFormat;
	}
	private WritableCellFormat getProjectFormat()
	{
		WritableFont projFont = new WritableFont(WritableFont.TIMES, 18,WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
		WritableCellFormat projFormat = new WritableCellFormat (projFont);
		try
		{
			projFormat.setAlignment(Alignment.CENTRE);
		}
		catch (WriteException e)
		{
		}
		return projFormat;
	}
	public void getTopTenSheet(HttpServletRequest request,
			HttpServletResponse response) throws DBException,
			StatisticException
	{
		StatisticDao sd = new StatisticDao();
		ArrayList<RecordBean> list = sd.getTopTen();	
		try
		{	
			response.setHeader("Content-disposition", "attachment;filename=Top_Ten_Tools.xls");
			response.setContentType("application/msexcel;charset=UTF-8");
			OutputStream xls = response.getOutputStream();
			
			WritableWorkbook workbook = Workbook.createWorkbook(xls);
			WritableSheet worksheet = workbook.createSheet("Top Ten", 0);
			worksheet.getSettings().setDefaultColumnWidth(20);
			worksheet.mergeCells(2, 0, 3, 0);
			Label proj = new Label(2, 0, "Top Ten Instrument", this.getProjectFormat());
			worksheet.addCell(proj);
			WritableCellFormat titleFormat = this.getTitleFormat();
			Label title1 = new Label(0, 2, "Bar-code",titleFormat);
			Label title2 = new Label(1, 2, "Instrument name",titleFormat);
			Label title3 = new Label(2, 2, "Price",titleFormat);
			Label title4 = new Label(3, 2, "In Lab",titleFormat);
			Label title5 = new Label(4, 2, "Create date",titleFormat);
			Label title6 = new Label(5, 2,"degree",titleFormat);
			worksheet.addCell(title1);
			worksheet.addCell(title2);
			worksheet.addCell(title3);
			worksheet.addCell(title4);
			worksheet.addCell(title5);
			worksheet.addCell(title6);
			int i = 3;
			WritableCellFormat cellFormat = this.getContentFormat();
			for(Iterator<RecordBean> iter = list.iterator();iter.hasNext();)
			{
				RecordBean rb = iter.next();
				Label lable1 = new Label(0, i, rb.getBarcode(),cellFormat);
				Label lable2 = new Label(1, i, rb.getInsName(),cellFormat);
				Label lable3 = new Label(2, i, rb.getPrice(),cellFormat);
				Label lable4 = new Label(3, i, rb.getIfIn(),cellFormat);
				Label lable5 = new Label(4, i, rb.getCreateDate().toString(),cellFormat);
				jxl.write.Number lable6 = new jxl.write.Number(5,i,rb.getDegree(),cellFormat);
				worksheet.addCell(lable1);
				worksheet.addCell(lable2);
				worksheet.addCell(lable3);
				worksheet.addCell(lable4);
				worksheet.addCell(lable5);
				worksheet.addCell(lable6);
				i++;
			}
			workbook.write();
			workbook.close();
		}
		catch (Exception e)
		{
			throw new StatisticException("Fail to generate a sheet for top ten instruments!");
		}
	}

	public void getIn(HttpServletRequest request,
			HttpServletResponse response) throws DBException,
			StatisticException
	{
		StatisticDao sd = new StatisticDao();
		ArrayList<InstrumentBean> list = sd.getIn();
		try
		{	
			response.setHeader("Content-disposition", "attachment;filename=Tools_In_Lab.xls");
			response.setContentType("application/msexcel;charset=UTF-8");
			OutputStream xls = response.getOutputStream();
			
			WritableWorkbook workbook = Workbook.createWorkbook(xls);
			WritableSheet worksheet = workbook.createSheet("Tools_In_Lab", 0);
			worksheet.mergeCells(2, 0, 3, 0);
			Label proj = new Label(2, 0, "Instrument,In The Lab", this.getProjectFormat());
			worksheet.addCell(proj);
			worksheet.getSettings().setDefaultColumnWidth(20);
			WritableCellFormat titleFormat = this.getTitleFormat();
			Label title1 = new Label(0, 2, "Bar-code",titleFormat);
			Label title2 = new Label(1, 2, "Instrument name",titleFormat);
			Label title3 = new Label(2, 2, "Price",titleFormat);
			Label title4 = new Label(3, 2, "In Lab",titleFormat);
			Label title5 = new Label(4, 2, "Create date",titleFormat);
			Label title6 = new Label(5, 2, "Remark",titleFormat);
			worksheet.addCell(title1);
			worksheet.addCell(title2);
			worksheet.addCell(title3);
			worksheet.addCell(title4);
			worksheet.addCell(title5);
			worksheet.addCell(title6);
			int i = 3;
			WritableCellFormat cellFormat = this.getContentFormat();
			for(Iterator<InstrumentBean> iter = list.iterator();iter.hasNext();)
			{
				InstrumentBean ins = iter.next();
				Label lable1 = new Label(0, i, ins.getBarcode(),cellFormat);
				Label lable2 = new Label(1, i, ins.getName(),cellFormat);
				Label lable3 = new Label(2, i, ins.getPrice(),cellFormat);
				Label lable4 = new Label(3, i, ins.getIfin(),cellFormat);
				Label lable5 = new Label(4, i, ins.getDate().toString(),cellFormat);
				Label lable6 = new Label(5, i,ins.getRemark(),cellFormat);
				worksheet.addCell(lable1);
				worksheet.addCell(lable2);
				worksheet.addCell(lable3);
				worksheet.addCell(lable4);
				worksheet.addCell(lable5);
				worksheet.addCell(lable6);
				i++;
			}
			workbook.write();
			workbook.close();
		}
		catch (Exception e)
		{
			throw new StatisticException("Fail to generate a sheet for instruments that are in Lab!");
		}
	}

	public void getOut(HttpServletRequest request,
			HttpServletResponse response) throws DBException,
			StatisticException
	{
		StatisticDao sd = new StatisticDao();
		ArrayList<RecordBean> list = sd.getOut();
		try
		{	
			response.setHeader("Content-disposition", "attachment;filename=Tools_Not_In_Lab.xls");
			response.setContentType("application/msexcel;charset=UTF-8");
			OutputStream xls = response.getOutputStream();
			
			WritableWorkbook workbook = Workbook.createWorkbook(xls);
			WritableSheet worksheet = workbook.createSheet("Tools_Not_In_Lab", 0);
			worksheet.mergeCells(3, 0, 4, 0);
			Label proj = new Label(3, 0, "Instrument, Not In The Lab", this.getProjectFormat());
			worksheet.addCell(proj);
			worksheet.getSettings().setDefaultColumnWidth(20);
			WritableCellFormat titleFormat = this.getTitleFormat();
			Label title1 = new Label(0, 2, "Bar-code",titleFormat);
			Label title2 = new Label(1, 2, "Instrument name",titleFormat);
			Label title3 = new Label(2, 2, "Price",titleFormat);
			Label title4 = new Label(3, 2, "In Lab",titleFormat);
			Label title5 = new Label(4, 2, "User ID",titleFormat);
			Label title6 = new Label(5, 2, "FirstName",titleFormat);
			Label title7 = new Label(6, 2, "LastName",titleFormat);
			Label title8 = new Label(7, 2, "Email",titleFormat);
			Label title9 = new Label(8, 2, "Borrow Date",titleFormat);
			Label title10 = new Label(9,2, "Back Date",titleFormat);
			worksheet.addCell(title1);
			worksheet.addCell(title2);
			worksheet.addCell(title3);
			worksheet.addCell(title4);
			worksheet.addCell(title5);
			worksheet.addCell(title6);
			worksheet.addCell(title7);
			worksheet.addCell(title8);
			worksheet.addCell(title9);
			worksheet.addCell(title10);
			int i = 3;
			WritableCellFormat cellFormat = this.getContentFormat();
			for(Iterator<RecordBean> iter = list.iterator();iter.hasNext();)
			{
				RecordBean rb = iter.next();
				Label lable1 = new Label(0, i, rb.getBarcode(),cellFormat);
				Label lable2 = new Label(1, i, rb.getInsName(),cellFormat);
				Label lable3 = new Label(2, i, rb.getPrice(),cellFormat);
				Label lable4 = new Label(3, i, rb.getIfIn(),cellFormat);
				Label lable5 = new Label(4, i, rb.getUserID(),cellFormat);
				Label lable6 = new Label(5, i, rb.getFirstname(),cellFormat);
				Label lable7 = new Label(6, i, rb.getLastname(),cellFormat);
				Label lable8 = new Label(7, i, rb.getEmail(),cellFormat);
				Label lable9 = new Label(8, i, rb.getBorrowDate().toString(),cellFormat);
				Label lable10 = new Label(9, i, rb.getSBackDate().toString(),cellFormat);
				worksheet.addCell(lable1);
				worksheet.addCell(lable2);
				worksheet.addCell(lable3);
				worksheet.addCell(lable4);
				worksheet.addCell(lable5);
				worksheet.addCell(lable6);
				worksheet.addCell(lable7);
				worksheet.addCell(lable8);
				worksheet.addCell(lable9);
				worksheet.addCell(lable10);
				i++;
			}
			workbook.write();
			workbook.close();
		}
		catch (Exception e)
		{
			throw new StatisticException("Fail to generate a sheet for instruments that are not in Lab!");
		}
	}

	public void getAllTools(HttpServletRequest request,
			HttpServletResponse response) throws DBException,
			StatisticException
	{
		StatisticDao sd = new StatisticDao();
		ArrayList<InstrumentBean> list = sd.getAllIns();
		try
		{	
			response.setHeader("Content-disposition", "attachment;filename=Tools_ALL.xls");
			response.setContentType("application/msexcel;charset=UTF-8");
			OutputStream xls = response.getOutputStream();
			
			WritableWorkbook workbook = Workbook.createWorkbook(xls);
			WritableSheet worksheet = workbook.createSheet("Tools_ALL", 0);
			worksheet.mergeCells(2, 0, 3, 0);
			Label proj = new Label(2, 0, "All  Instruments", this.getProjectFormat());
			worksheet.addCell(proj);
			worksheet.getSettings().setDefaultColumnWidth(20);
			WritableCellFormat titleFormat = this.getTitleFormat();
			Label title1 = new Label(0, 2, "Bar-code",titleFormat);
			Label title2 = new Label(1, 2, "Instrument name",titleFormat);
			Label title3 = new Label(2, 2, "Price",titleFormat);
			Label title4 = new Label(3, 2, "In Lab",titleFormat);
			Label title5 = new Label(4, 2, "Create date",titleFormat);
			Label title6 = new Label(5, 2, "Remark",titleFormat);
			worksheet.addCell(title1);
			worksheet.addCell(title2);
			worksheet.addCell(title3);
			worksheet.addCell(title4);
			worksheet.addCell(title5);
			worksheet.addCell(title6);
			int i = 3;
			WritableCellFormat cellFormat = this.getContentFormat();
			for(Iterator<InstrumentBean> iter = list.iterator();iter.hasNext();)
			{
				InstrumentBean ins = iter.next();
				Label lable1 = new Label(0, i, ins.getBarcode(),cellFormat);
				Label lable2 = new Label(1, i, ins.getName(),cellFormat);
				Label lable3 = new Label(2, i, ins.getPrice(),cellFormat);
				Label lable4 = new Label(3, i, ins.getIfin(),cellFormat);
				Label lable5 = new Label(4, i, ins.getDate().toString(),cellFormat);
				Label lable6 = new Label(5, i,ins.getRemark(),cellFormat);
				worksheet.addCell(lable1);
				worksheet.addCell(lable2);
				worksheet.addCell(lable3);
				worksheet.addCell(lable4);
				worksheet.addCell(lable5);
				worksheet.addCell(lable6);
				i++;
			}
			workbook.write();
			workbook.close();
		}
		catch (Exception e)
		{
			throw new StatisticException("Fail to generate a sheet for all instruments!");
		}
	}

	public void getAllUsers(HttpServletRequest request,
			HttpServletResponse response) throws DBException,
			StatisticException
	{
		StatisticDao sd = new StatisticDao();
		ArrayList<UserBean> list = sd.getAllUsers();
		try
		{	
			response.setHeader("Content-disposition", "attachment;filename=USERS_ALL.xls");
			response.setContentType("application/msexcel;charset=UTF-8");
			OutputStream xls = response.getOutputStream();
			
			WritableWorkbook workbook = Workbook.createWorkbook(xls);
			WritableSheet worksheet = workbook.createSheet("USERS_ALL", 0);
			worksheet.mergeCells(3, 0, 4, 0);
			Label proj = new Label(3, 0, "All  Users", this.getProjectFormat());
			worksheet.addCell(proj);
			worksheet.getSettings().setDefaultColumnWidth(18);
			WritableCellFormat titleFormat = this.getTitleFormat();
			Label title1 = new Label(0, 2, "UserID",titleFormat);
			Label title2 = new Label(1, 2, "FirstName",titleFormat);
			Label title3 = new Label(2, 2, "LastName",titleFormat);
			Label title4 = new Label(3, 2, "Email",titleFormat);
			Label title5 = new Label(4, 2, "Phone",titleFormat);
			Label title6 = new Label(5, 2, "Age",titleFormat);
			Label title7 = new Label(6, 2, "Gender",titleFormat);
			Label title8 = new Label(7, 2, "Status",titleFormat);
			Label title9 = new Label(8, 2, "limit",titleFormat);
			Label title10 = new Label(9, 2, "Date",titleFormat);
			Label title11 = new Label(10, 2, "Remark",titleFormat);
			worksheet.addCell(title1);
			worksheet.addCell(title2);
			worksheet.addCell(title3);
			worksheet.addCell(title4);
			worksheet.addCell(title5);
			worksheet.addCell(title6);
			worksheet.addCell(title7);
			worksheet.addCell(title8);
			worksheet.addCell(title9);
			worksheet.addCell(title10);
			worksheet.addCell(title11);
			int i = 3;
			WritableCellFormat cellFormat = this.getContentFormat();
			for(Iterator<UserBean> iter = list.iterator();iter.hasNext();)
			{
				UserBean ub = iter.next();
				Label lable1 = new Label(0, i, ub.getUserID(),cellFormat);
				Label lable2 = new Label(1, i, ub.getFirstname(),cellFormat);
				Label lable3 = new Label(2, i, ub.getLastname(),cellFormat);
				Label lable4 = new Label(3, i, ub.getEmail(),cellFormat);
				Label lable5 = new Label(4, i, ub.getPhone(),cellFormat);
				Label lable6 = new Label(5, i, ub.getAge(),cellFormat);
				Label lable7 = new Label(6, i, ub.getGender(),cellFormat);
				Label lable9 = new Label(7, i, ub.getDays(),cellFormat);
				Label lable10 = new Label(8, i, ub.getDate().toString(),cellFormat);
				Label lable11 = new Label(9, i,ub.getRemark(),cellFormat);
				worksheet.addCell(lable1);
				worksheet.addCell(lable2);
				worksheet.addCell(lable3);
				worksheet.addCell(lable4);
				worksheet.addCell(lable5);
				worksheet.addCell(lable6);
				worksheet.addCell(lable7);
				worksheet.addCell(lable9);
				worksheet.addCell(lable10);
				worksheet.addCell(lable11);
				i++;
			}
			workbook.write();
			workbook.close();
		}
		catch (Exception e)
		{
			throw new StatisticException("Fail to generate a sheet for all users!");
		}
	}

}
